import json
import os
import re
import uuid
import random
from datetime import datetime
#1头像
#2名字 心智谷_da231
#3评论区回复
#4回帖时间2024-04-01 15:47:15

# 获取当前的日期和时间
now = datetime.now()
# 将日期和时间格式化为MySQL的DATETIME格式
comment_time = now.strftime('%Y-%m-%d %H:%M:%S')
avatar = "/images/avatar/default.jpg"  # 用户头像

def generate_uuid():
    # 生成 UUID
    generated_uuid = uuid.uuid4()

    return str(generated_uuid).replace('-', '')


def create_insert_query(data, output_file):
    with open(output_file, 'w', encoding='utf-8') as f:
        cnt = 0
        for question in data:
            id = generate_uuid()  # 用户id = 用户姓名
            # 获取第一张表
            ques_id = question['ques_id']  # 帖子id-字符串
            if ques_id == "577bcc914f9e55d5e4e4f82f9f00e7d4":
                continue
            title = question['ques_info']['title']  # 字符串
            content = question['ques_info']['content']  # 字符串
            if '“' in content or '"' in content:
                continue
            answer_count = question['ques_info']['answer_count']  # 数字
            ques_label = ','.join(question['ques_info']['ques_label'])  # 列表
            query1 = f"INSERT INTO `post_info` (post_id, user_name, user_avatar, title, content, ques_label) "\
                     f"VALUES ('{ques_id}', '{id}', '{avatar}', '{title}', '{content}', '{ques_label}');"
            f.write(query1+"\n")

            # 获取第二张表
            views = random.randint(0,300)  # 数字
            collect = question['staic_info']['collect']  # 数字
            zanForPost = random.randint(0, 20)  # 数字

            query2 = f"INSERT INTO static_info (id, post_id, views, collect, zan, comments) " \
                     f"VALUES (null, '{ques_id}', {views}, {collect}, {zanForPost}, {answer_count});"
            f.write(query2+"\n")

            # 获取第三张表
            for answer in question['answers_info']:
                recommend_flag = answer['recommend_flag']  # 字符串
                answer_content = answer['content']  # 字符串
                zan = random.randint(0, 20)  # 数字
                query3 = f"INSERT INTO comment_info (id, post_id, user_name, user_avatar, recomment_flag, content, zan) " \
                         f"VALUES (null, '{ques_id}', '{id}', '{avatar}', '{recommend_flag}', '{answer_content}', {zan});"
                f.write(query3+"\n")

            # 获取第四张表1
            query4 = f"INSERT INTO `user` (`id`, `username`, `password`) " \
                     f"VALUES ('{id}', '{id}', '5f329d3ac22671f7b214c461e58c27f3');"
            f.write(query4+"\n\n\n")
            cnt += 1
            if(cnt == 200):
                break

def main(input_dir, output_dir):
    input_file = os.path.join(input_dir, 'ques_ans1.json')
    output_file = os.path.join(output_dir, 'test7.sql')
    with open(input_file, 'r', encoding='utf-8') as f:  # 指定以UTF-8编码打开文件
        data = json.load(f)
        create_insert_query(data, output_file)


# 请将以下路径替换为你的实际路径
input_dir = 'G:\data'
output_dir = 'G:\data'
main(input_dir, output_dir)
